package com.tsj.model.utils;

import com.tsj.model.model.vo.AttachmentInfo;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.Ole10Native;
import org.apache.poi.poifs.filesystem.Ole10NativeException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;

/**
 * @author taoshujian
 * @version 1.0
 * @since 2025-10-24 11:50:04
 */
public class XlsxImageExtractor {

    public static void main(String[] args) throws IOException {
        //URL url = new URL("http://192.168.0.63:9000/digital-middle-platform/files/resource/resource/68fae809e4b0c594140f49b4.xlsx");
        URL url = new URL("http://192.168.0.63:9000/digital-middle-platform/files/resource/resource/68fb19ece4b0c594140f49bc.xlsx");
        Path excelPath = Paths.get("S://"); // 也可改为 .xlsoutputDir
        try (InputStream is = url.openStream();
             Workbook workbook = WorkbookFactory.create(is)) {
            if (workbook instanceof XSSFWorkbook xwb) {
                extractImagesFromXlsx(xwb.getSheetAt(0), excelPath);
            }
        } catch (Ole10NativeException e) {
            throw new RuntimeException(e);
        }
    }

    public static void extractImagesFromXlsx(XSSFSheet sheet, Path outputDir) throws IOException, Ole10NativeException {
        // 获取工作表中的绘图容器（所有附件都在这里）
        XSSFDrawing drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            System.out.println("无附件");
            return; // 无附件
        }

        // 遍历所有形状（每个形状可能是图片或OLE附件）
        for (XSSFShape shape : drawing.getShapes()) {
            // 获取附件的坐标锚点（确定位置）
            XSSFAnchor anchor = shape.getAnchor();
            if (!(anchor instanceof XSSFClientAnchor)) {
                continue; // 跳过非单元格关联的附件
            }
            XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor;

            // 根据锚点计算关联的单元格地址（如"A1"）
            String cellAddress = getCellAddressByAnchor(clientAnchor);

            // 提取附件信息（名称、类型、内容）
            if (shape instanceof XSSFPicture) {
                // 处理图片附件
                XSSFPicture picture = (XSSFPicture) shape;
                XSSFPictureData pictureData = picture.getPictureData();
                String ext = pictureData.suggestFileExtension();
                String fileName = "image_" + System.currentTimeMillis() + "." + (ext != null ? ext : "jpg");
                byte[] content = pictureData.getData();
                AttachmentInfo attachmentInfo = new AttachmentInfo(fileName, cellAddress, "图片", content);
                System.out.println(attachmentInfo);
            } else if (shape instanceof XSSFObjectData) {
                // 处理OLE附件（如PDF、Word等）
                XSSFObjectData objectData = (XSSFObjectData) shape;
                String fileName = objectData.getFileName();
                if (fileName == null || fileName.isEmpty()) {
                    fileName = "attachment_" + System.currentTimeMillis() + ".bin"; // 未知文件名
                }
                byte[] content = objectData.getObjectData();
                AttachmentInfo attachmentInfo = new AttachmentInfo(fileName, cellAddress, "OLE附件", content);
                System.out.println(attachmentInfo);

                InputStream inputStream = new ByteArrayInputStream(content);
                POIFSFileSystem fs = new POIFSFileSystem(inputStream);
                Ole10Native nativeObject = Ole10Native.createFromEmbeddedOleObject( fs);
                System.out.println("文件名："+nativeObject.getLabel2());

                BufferedInputStream bis = new BufferedInputStream(objectData.getObjectPart().getInputStream());
                OutputStream outputStream = new FileOutputStream(new File(outputDir+"/"+nativeObject.getLabel2()));
                BufferedOutputStream bos = new BufferedOutputStream(outputStream);

                byte[] buffer = new byte[1024 * 8]; // 8KB 缓冲区
                int bytesRead;
                // 循环读取输入流数据，写入输出流
                while ((bytesRead = bis.read(buffer)) != -1) {
                    bos.write(buffer, 0, bytesRead);
                }
                bos.flush(); // 强制刷新缓冲区，确保数据写入文件

                //saveInputStreamToFile(objectData.getObjectPart().getInputStream(), outputDir+"/"+nativeObject.getLabel2());
                //saveInputStreamToFile(objectData.getObjectPart().getInputStream(), outputDir+"/"+nativeObject.getLabel2());
            }
        }
    }
    /**
     * 根据附件的锚点坐标计算其主要关联的单元格地址（如"A1"）
     * 规则：取附件左上角所在的单元格
     */
    private static String getCellAddressByAnchor(XSSFClientAnchor anchor) {
        // 锚点的起始行和起始列（左上角位置）
        int rowIndex = anchor.getRow1();
        int colIndex = anchor.getCol1();

        // 转换列号为字母（如0→A，1→B...）
        String colLetter = getColumnLetter(colIndex);
        // 行号从1开始（Excel中行号从1起始）
        int rowNumber = rowIndex + 1;

        return colLetter + rowNumber;
    }

    /**
     * 将列索引（0-based）转换为Excel列字母（如0→A，25→Z，26→AA）
     */
    private static String getColumnLetter(int colIndex) {
        if (colIndex < 0) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        while (colIndex >= 0) {
            int remainder = colIndex % 26;
            sb.append((char) ('A' + remainder));
            colIndex = (colIndex / 26) - 1;
            if (colIndex < 0) {
                break;
            }
        }
        return sb.reverse().toString();
    }

    /**
     * 将 InputStream 内容写入本地文件
     * @param inputStream 输入流（需确保非空且已打开）
     * @param targetPath 本地目标文件路径（例如："D:/test/output.txt"）
     * @throws IOException 流操作异常
     */
    public static void saveInputStreamToFile(InputStream inputStream, String targetPath) throws IOException {
        // 确保目标目录存在，不存在则创建
        File targetFile = new File(targetPath);
        File parentDir = targetFile.getParentFile();
        if (parentDir != null && !parentDir.exists()) {
            parentDir.mkdirs(); // 递归创建目录
        }

        // 使用 try-with-resources 自动关闭流（无需手动调用 close()）
        try (OutputStream outputStream = new FileOutputStream(targetFile);
             // 缓冲流提升读写效率（大文件推荐使用）
             BufferedInputStream bis = new BufferedInputStream(inputStream);
             BufferedOutputStream bos = new BufferedOutputStream(outputStream)) {

            byte[] buffer = new byte[1024 * 800]; // 8KB 缓冲区
            int bytesRead;
            // 循环读取输入流数据，写入输出流
            while ((bytesRead = bis.read(buffer)) != -1) {
                bos.write(buffer, 0, bytesRead);
            }
            bos.flush(); // 强制刷新缓冲区，确保数据写入文件
        }
    }
}
